package com.jiedeng.controller;

import com.jiedeng.dao.*;
import com.jiedeng.mapper.ReportMapper;
import com.jiedeng.model.*;
import com.jiedeng.util.ExcelUtil;
import com.jiedeng.util.QiniuUtil;
import com.qiniu.util.StringUtils;
import com.squareup.okhttp.OkHttpClient;
import com.squareup.okhttp.Request;
import com.squareup.okhttp.Response;
import com.squareup.okhttp.ResponseBody;
import io.swagger.annotations.ApiOperation;
import okio.BufferedSink;
import okio.BufferedSource;
import okio.Okio;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.http.HttpRequest;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.awt.image.BufferedImage;
import java.io.*;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

//报表
@RestController("/reporter")
//@RequestMapping("/reporter")
public class ReporterController {

    @Value("${config.taskTempPath}")
    private String taskTempPath;

    @Value("${config.projectTempPath}")
    private String projectTempPath;
    @Value("${config.project2TempPath}")
    private String project2TempPath;

    @Value("${config.checkTempPath}")
    private String checkTempPath;
    @Value("${config.check2TempPath}")
    private String check2TempPath;

    @Value("${config.winReporterPath}")
    private String winPath;

    @Value("${config.linuxReporterPath}")
    private String linuxPath;

    @Value("${config.prefix}")
    private String prefix;

    @Autowired
    ITaskRepository taskRepository;

    @Autowired
    IProjectRepository projectRepository;

    @Autowired
    IProjectCorpRepository projectCorpRepository;

    @Autowired
    ICheckRepository checkRepository;

    @Autowired
    ICorpRepository corpRepository;

    @Autowired
    ICorpPostRepository corpPostRepository;

    @Autowired
    IProjectPersonRepository projectPersonRepository;

    @Autowired
    IPersonRepository personRepository;

    @Autowired
    ICheckCountRepository checkCountRepository;

    @Autowired
    ICorpPropRepository corpPropRepository;

    @Autowired
    ICheckItemRepository checkItemRepository;
    @Autowired
    ICheckItemRuleRepository checkItemRuleRepository;

    @Autowired
    IFileRecordRepository fileRecordRepository;

    @Autowired
    ISpecRepository specRepository;
    @Autowired
    ReportMapper reportMapper;
    String taskSheetName = "整改通知单";

    String projectSheetName = "项目评分情况";

    String checkSheetName = "检查表";

    static int count = 0;

    @ApiOperation(value = "整改通知单")
    @RequestMapping(value = "/task", method = RequestMethod.GET,produces="text/html; charset=UTF-8")
    public String taskReporter(String taskId) {
        String path = "";
        String os = System.getProperty("os.name");
        if (os.toLowerCase().startsWith("win")) {
            path = winPath;
        } else {
            path = linuxPath;
        }
        String keyName = "整改闭环单" + GetNowDate() + ".xlsx";
        // String keyName ="zhenggaitongzhidan"+ GetNowDate() + ".xlsx";
        String fileName = path + keyName;
        downloadSmallFile(taskTempPath, fileName);

        Task task = taskRepository.findById(taskId).get();
        FileInputStream in = null;
        XSSFWorkbook wb = null;
        try {
            in = new FileInputStream(fileName);
            wb = new XSSFWorkbook(in);
            XSSFSheet sheet = wb.getSheet(taskSheetName);

            //检查组织单位
            Row row = sheet.getRow(1);
            Cell cell = row.createCell(1);
            Optional<Corp> corp = corpRepository.findById(task.getTaskCorpId());
            if (corp.isPresent()) cell.setCellValue(corp.get().getCaption());
            // 项目名称，被查地点

            row = sheet.getRow(2);

            List<ProjectCorp> projectCorp = projectCorpRepository.findProjectCorpSQL(taskId, task.getProjectId(), 2);
            //List<ProjectCorp> projectCorp = projectCorpRepository.findByCorpPropIdAndProjectId(2, task.getProjectId());
            if (projectCorp != null && projectCorp.size() > 0) {
                cell = row.getCell(1);
                Corp corp1 = corpRepository.findAllById(projectCorp.get(0).getCorpId());
                cell.setCellValue(corp1.getCaption());
            }
            cell = row.getCell(3);
            Optional<Project> project = projectRepository.findById(task.getProjectId());
            if (project.isPresent()) cell.setCellValue(project.get().getCaption());
            row = sheet.getRow(3);
            cell = row.getCell(1);
            //if (project.isPresent()) cell.setCellValue(project.get().getCaption());
            String gps = task.getGPS();
            if (!StringUtils.isNullOrEmpty(gps)) {
                String[] address = gps.split("#");
                cell.setCellValue(address[0]);
            }
            //检查时间
            row = sheet.getRow(4);
            cell = row.createCell(1);
            Date exeDate = task.getExcuteTime();
            if (exeDate != null) {
                String exeDateStr = (new SimpleDateFormat("yyyy年MM月dd日")).format(exeDate);
                cell.setCellValue(exeDateStr);
            }
            //检查人员
            row = sheet.getRow(6);
            cell = row.createCell(1);
            String[] persons = task.getExcutePersonId().split(",");
            String personStr = "";
            for (String person : persons) {
                if (!person.isEmpty()) {
                    Person p = personRepository.findById(Long.parseLong(person)).get();
                    personStr = personStr + p.getName() + ",";
                }
            }
            personStr = personStr.substring(0, personStr.length() - 1);
            cell.setCellValue(personStr);

            List<Check> problemChecks = checkRepository.findByTaskIdAndIsCloseFileAndIsPub(taskId, true, true);

            //第10行开始循环列出问题和整改数据
            int startListIndex = 9;
            Row srcRow = sheet.getRow(startListIndex);
            for (Check pCheck : problemChecks) {
                row = sheet.getRow(startListIndex);
                if (row == null) row = sheet.createRow(startListIndex);
                row.setHeight(srcRow.getHeight());
                row.setRowStyle(srcRow.getRowStyle());
                //问题描述
                cell = row.createCell(0);
                cell.setCellValue(pCheck.getExpressionDesc());
                XSSFDrawing patriarch = sheet.createDrawingPatriarch();
                //问题照片
                List<FileRecord> pFiles = fileRecordRepository.findByCheckIdAndCheckClose(pCheck.getId(), true);
                //整改照片
                List<FileRecord> fFiles = fileRecordRepository.findByCheckIdAndCheckClose(pCheck.getId(), false);

                cell = row.getCell(1);
                short temp = 0;
                int pHeight = 10;
                for (FileRecord fileRecord : pFiles) {
                    if (!fileRecord.getOldPhotoPath().isEmpty()) {
                        String tempFile = path + GetNowDate() + String.valueOf(temp) + ".jpg";
                        boolean bResult = downloadSmallFile(prefix + fileRecord.getOldPhotoPath() + "?imageView2/0/w/150", tempFile);
                        if (bResult) {
                            BufferedImage image = ImageIO.read(new File(tempFile));
                            int height = image.getHeight();
                            pHeight += height;
                            row.setHeightInPoints(pHeight);

                            InputStream is = new FileInputStream(tempFile);
                            byte[] bytes = IOUtils.toByteArray(is);

                            int pictureIdx = wb.addPicture(bytes, XSSFWorkbook.PICTURE_TYPE_JPEG);

                            CreationHelper helper = wb.getCreationHelper();
                            //Drawing drawing = sheet.createDrawingPatriarch();
                            ClientAnchor anchor = helper.createClientAnchor();

                            anchor.setCol1(1);
                            anchor.setRow1(startListIndex);

                            Picture pict = patriarch.createPicture(anchor, pictureIdx);
                            pict.resize();
                        }
                        temp++;
                    }
                }


                //整改意见
                cell = row.createCell(2);
                cell.setCellValue(pCheck.getCloseMemo());
                int fHeight = 10;
                for (FileRecord fileRecord : fFiles) {
                    if (fileRecord.getOldPhotoPath() != null && !fileRecord.getOldPhotoPath().isEmpty()) {
                        String tempFile = path + GetNowDate() + String.valueOf(temp) + ".jpg";

                        boolean bResult = downloadSmallFile(prefix + fileRecord.getOldPhotoPath() + "?imageView2/0/w/150", tempFile);

                        if (bResult) {
                            BufferedImage image = ImageIO.read(new File(tempFile));
                            int height = image.getHeight();
                            pHeight += height;
                            if (fHeight > pHeight) {
                                row.setHeightInPoints(fHeight);
                            }

                            InputStream is = new FileInputStream(tempFile);
                            byte[] bytes = IOUtils.toByteArray(is);

                            int pictureIdx = wb.addPicture(bytes, XSSFWorkbook.PICTURE_TYPE_JPEG);

                            CreationHelper helper = wb.getCreationHelper();
                            //Drawing drawing = sheet.createDrawingPatriarch();
                            ClientAnchor anchor = helper.createClientAnchor();

                            anchor.setCol1(3);
                            anchor.setRow1(startListIndex);

                            Picture pict = patriarch.createPicture(anchor, pictureIdx);
                            pict.resize();
                        }
                    }
                }
                startListIndex++;
            }

            FileOutputStream out = new FileOutputStream(fileName);
            wb.write(out);
            in.close();
            out.close();

            QiniuUtil qiniuUtil = new QiniuUtil();
            qiniuUtil.upload(fileName, keyName);
            return prefix + keyName;
        } catch (IOException e) {
            System.out.println(e.toString());
            return "server error";
        } finally {
            try {
                in.close();
            } catch (IOException e) {
                System.out.println(e.toString());
            }
        }
    }

    @ApiOperation(value = "项目评分表")
    @RequestMapping(value = "/project", method = RequestMethod.GET,produces="text/html; charset=UTF-8")
    public String projectReport(String taskId) {
        String path = "";
        String os = System.getProperty("os.name");
        if (os.toLowerCase().startsWith("win")) {
            path = winPath;
        } else {
            path = linuxPath;
        }
        String keyName = "项目评分表" + GetNowDate() + ".xlsx";
        // String keyName ="xiangmupingfenbiao"+ GetNowDate() + ".xlsx";
        String fileName = path + keyName;
        boolean rst = downloadSmallFile(projectTempPath, fileName);
        DecimalFormat decimalFormat = new DecimalFormat("0.000");
        Task task = taskRepository.getOne(taskId);
        FileInputStream in = null;
        XSSFWorkbook wb = null;
        try {
            in = new FileInputStream(fileName);
            wb = new XSSFWorkbook(in);
            XSSFSheet sheet = wb.getSheet(projectSheetName);

            //项目名称
            Row row = sheet.getRow(1);
            Cell cell = row.getCell(1);

            Project project = projectRepository.getOne(task.getProjectId());
            cell.setCellValue(project.getCaption());

            //执行时间
            cell = row.createCell(7);
            cell.setCellValue(GetFormatDate(task.getExcuteTime()));

            //建设管理单位
            row = sheet.getRow(4);
            cell = row.getCell(0);
            long corpPropId = 2;
            List<ProjectCorp> projectCorps = projectCorpRepository.findProjectCorpSQL(taskId, task.getProjectId(), corpPropId);
            //List<ProjectCorp> projectCorps = projectCorpRepository.findByCorpPropIdAndProjectId(corpPropId, task.getProjectId());
            Corp corp = corpRepository.getOne(projectCorps.get(0).getCorpId());
            cell.setCellValue(corp.getCaption());

            //监理单位
            corpPropId = 4;
            //List<ProjectCorp> projectCorps1 = projectCorpRepository.findByCorpPropIdAndProjectId(corpPropId, task.getProjectId());
            List<ProjectCorp> projectCorps1 = projectCorpRepository.findProjectCorpSQL(taskId, task.getProjectId(), corpPropId);
            Corp checkCorp = corpRepository.getOne(projectCorps1.get(0).getCorpId());
            cell = row.getCell(3);
            cell.setCellValue(checkCorp.getCaption());

            //施工单位
            corpPropId = 5;
            List<ProjectCorp> projectCorps2 = projectCorpRepository.findProjectCorpSQL(taskId, task.getProjectId(), corpPropId);
            //List<ProjectCorp> projectCorps2 = projectCorpRepository.findByCorpPropIdAndProjectId(corpPropId, task.getProjectId());

            Corp exeCorp = corpRepository.getOne(projectCorps2.get(0).getCorpId());
            cell = row.getCell(6);
            cell.setCellValue(exeCorp.getCaption());

            //建设管理单位岗位
            int buildRowIndex = 7;
            List<CorpPost> buildCorpPosts = corpPostRepository.findByIdBetween(9200, 9300);
            double allBuildTotal = 0;
            Row srcRow = sheet.getRow(buildRowIndex);
            for (CorpPost corpPost : buildCorpPosts) {
                row = sheet.getRow(buildRowIndex);
                if (row == null) row = sheet.createRow(buildRowIndex);
                row.setRowStyle(srcRow.getRowStyle());
                row.setHeight(srcRow.getHeight());
                cell = row.createCell(0);
                cell.setCellValue(corpPost.getCaption());
                List<ProjectPerson> projectPersonList = projectPersonRepository.findProjectPerson(taskId, task.getProjectId(), corpPost.getId());
                if (projectPersonList != null && projectPersonList.size() > 0) {
                   /* ProjectPerson projectPerson = projectPersonList.get(0);
                    Person person = personRepository.getOne(projectPerson.getPersonId());
                    cell = row.createCell(1);
                    cell.setCellValue(person.getName());*/
                    String personName = "";
                    for (ProjectPerson projectPerson : projectPersonList) {
                        Person person = personRepository.getOne(projectPerson.getPersonId());
                        if (person.getCorpId() == corp.getId()) {
                            personName = person.getName();
                            break;
                        }
                    }

                    cell = row.createCell(1);
                    cell.setCellValue(personName);
                }

                List<Check> checks = checkRepository.findByTaskIdAndIsPub(taskId, true);
                double total = 0;
                for (Check check : checks) {
                    List<CheckCount> counts = checkCountRepository.findByCheckIdAndPostId(check.getId(), corpPost.getId());
                  /*  if (counts != null && counts.size() > 0) {
                        String score = counts.get(0).getScoring();
                        total = total + Double.parseDouble(score);
                    }*/
                  /* boolean mainScoreCheck = true;
                        boolean mainScoreCheck1 = true;
                        for (CheckCount checkCount : counts) {
                            String score = checkCount.getScoring();
                            String score2 = checkCount.getScoring1();

                            if (!StringUtils.isNullOrEmpty(score)&&mainScoreCheck) {
                                total = total + Double.parseDouble(score);
                                mainScoreCheck=false;
                            }
                            if (!StringUtils.isNullOrEmpty(score2)&&mainScoreCheck1) {
                                total = total - Double.parseDouble(score2);
                                mainScoreCheck1=false;
                            }
                        }*/
                    if (counts != null && counts.size() > 0) {
                        for (CheckCount checkCount : counts) {
                            String score = checkCount.getScoring();
                            String score2 = checkCount.getScoring1();
                            if (!StringUtils.isNullOrEmpty(score)) {
                                total = total + Double.parseDouble(score);
                            }
                            if (!StringUtils.isNullOrEmpty(score2)) {
                                total = total - Double.parseDouble(score2);
                            }
                        }
                    }
                }
                cell = row.createCell(2);
                cell.setCellValue(decimalFormat.format(total));
                allBuildTotal += total;
                buildRowIndex++;
            }
            row = sheet.getRow(4);
            cell = row.getCell(2);
            cell.setCellValue(decimalFormat.format(allBuildTotal));

            //监理单位岗位
            buildRowIndex = 7;
            List<CorpPost> checkCorpPosts = corpPostRepository.findByIdBetween(9400, 9500);
            double allCheckTotal = 0;
            for (CorpPost corpPost : checkCorpPosts) {
                row = sheet.getRow(buildRowIndex);
                if (row == null) row = sheet.createRow(buildRowIndex);
                row.setRowStyle(srcRow.getRowStyle());
                row.setHeight(srcRow.getHeight());
                cell = row.createCell(3);
                cell.setCellValue(corpPost.getCaption());

                List<ProjectPerson> projectPersonList = projectPersonRepository.findProjectPerson(taskId, task.getProjectId(), corpPost.getId());
                //List<ProjectPerson> projectPersonList = projectPersonRepository.findByProjectIdAndPostId(task.getProjectId(), corpPost.getId());
                if (projectPersonList != null && projectPersonList.size() > 0) {
                  /*  ProjectPerson projectPerson = projectPersonList.get(0);
                    Person person = personRepository.getOne(projectPerson.getPersonId());
                    cell = row.createCell(4);
                    cell.setCellValue(person.getName());*/
                    String personName = "";
                    for (ProjectPerson projectPerson : projectPersonList) {
                        Person person = personRepository.getOne(projectPerson.getPersonId());
                        if (person.getCorpId() == checkCorp.getId()) {
                            personName = person.getName();
                            break;
                        }
                    }

                    cell = row.createCell(4);
                    cell.setCellValue(personName);
                }

                List<Check> checks = checkRepository.findByTaskIdAndIsPub(taskId, true);
                double total = 0;
                for (Check check : checks) {
                    List<CheckCount> counts = checkCountRepository.findByCheckIdAndPostId(check.getId(), corpPost.getId());
                    if (counts != null && counts.size() > 0) {
                        for (CheckCount checkCount : counts) {
                            String score = checkCount.getScoring();
                            String score2 = checkCount.getScoring1();
                            if (!StringUtils.isNullOrEmpty(score)) {
                                total = total + Double.parseDouble(score);
                            }
                            if (!StringUtils.isNullOrEmpty(score2)) {
                                total = total - Double.parseDouble(score2);
                            }
                        }
                    }
                }
                cell = row.createCell(5);
                cell.setCellValue(decimalFormat.format(total));
                allCheckTotal += total;
                buildRowIndex++;
            }
            row = sheet.getRow(4);
            cell = row.getCell(5);
            cell.setCellValue(decimalFormat.format(allCheckTotal));

            //施工单位岗位
            buildRowIndex = 7;
            List<CorpPost> exeCorpPosts = corpPostRepository.findByIdBetween(9500, 9600);
            double allExeTotal = 0;
            for (CorpPost corpPost : exeCorpPosts) {
                row = sheet.getRow(buildRowIndex);
                if (row == null) row = sheet.createRow(buildRowIndex);
                row.setRowStyle(srcRow.getRowStyle());
                row.setHeight(srcRow.getHeight());
                cell = row.createCell(6);
                cell.setCellValue(corpPost.getCaption());
                List<ProjectPerson> projectPersonList = projectPersonRepository.findProjectPerson(taskId, task.getProjectId(), corpPost.getId());
                //List<ProjectPerson> projectPersonList = projectPersonRepository.findByProjectIdAndPostId(task.getProjectId(), corpPost.getId());
                if (projectPersonList != null && projectPersonList.size() > 0) {
                    String personName = "";
                    for (ProjectPerson projectPerson : projectPersonList) {
                        Person person = personRepository.getOne(projectPerson.getPersonId());
                        if (person.getCorpId() == exeCorp.getId()) {
                            personName = person.getName();
                            break;
                        }
                    }
                    cell = row.createCell(7);
                    cell.setCellValue(personName);
                }

                List<Check> checks = checkRepository.findByTaskIdAndIsPub(taskId, true);
                double total = 0;
                for (Check check : checks) {
                    List<CheckCount> counts = checkCountRepository.findByCheckIdAndPostId(check.getId(), corpPost.getId());
                    if (counts != null && counts.size() > 0) {
                        for (CheckCount checkCount : counts) {
                            String score2 = checkCount.getScoring1();
                            String score = checkCount.getScoring();
                            if (!StringUtils.isNullOrEmpty(score)) {
                                total = total + Double.parseDouble(score);
                            }
                            if (!StringUtils.isNullOrEmpty(score2)) {
                                total = total - Double.parseDouble(score2);
                            }
                        }
                       /* String score = counts.get(0).getScoring();
                        String score2 = counts.get(0).getScoring1();
                        if(!StringUtils.isNullOrEmpty(score)){
                            total = total + Double.parseDouble(score);
                        }
                        if(!StringUtils.isNullOrEmpty(score2)){
                            total = total - Double.parseDouble(score2);
                        }*/
                    }
                }
                cell = row.createCell(8);
                cell.setCellValue(decimalFormat.format(total));
                allExeTotal += total;
                buildRowIndex++;
            }
            row = sheet.getRow(4);
            cell = row.getCell(8);
            cell.setCellValue(decimalFormat.format(allExeTotal));

            in.close();
            FileOutputStream out = new FileOutputStream(fileName);
            wb.write(out);
            out.close();

            QiniuUtil qiniuUtil = new QiniuUtil();
            qiniuUtil.upload(fileName, keyName);

            return prefix + keyName;
        } catch (IOException e) {
            e.printStackTrace();
            return e.toString();
        }
    }

    @ApiOperation(value = "检查表")
    @RequestMapping(value = "/check", method = RequestMethod.GET,produces="text/html; charset=UTF-8")
    public String checkReport(String taskId) {
        String path = "";
        String os = System.getProperty("os.name");
        if (os.toLowerCase().startsWith("win")) {
            path = winPath;
        } else {
            path = linuxPath;
        }
        String keyName = "检查表" + GetNowDate() + ".xlsx";
        // String keyName ="jianchabiao"+ GetNowDate() + ".xlsx";
        String fileName = path + keyName;
        downloadSmallFile(checkTempPath, fileName);

        //Task task = taskRepository.getOne(taskId);
        FileInputStream in = null;
        XSSFWorkbook wb = null;
        try {
            in = new FileInputStream(fileName);
            wb = new XSSFWorkbook(in);
            XSSFSheet sheet = wb.getSheet(checkSheetName);
            //XSSFCellStyle cellStyle2 = wb.createCellStyle();
            //XSSFDataFormat format = wb.createDataFormat();
            //cellStyle2.setDataFormat(format.getFormat("@"));
            //检查组织单位
            Row row = null;
            Cell cell = null;
            Row srcRow = sheet.getRow(3);
            Row titleRow = sheet.getRow(2);

            List<Check> checks = checkRepository.findByTaskIdAndIsPub(taskId, true);
            int startRow = 3;

            List<Spec> specs = specRepository.findAll();
            short specRowIndex = 1;
            for (Spec spec : specs) {
                Row tRow = null;
                if (specRowIndex == 1) {
                    tRow = titleRow;
                } else {
                    tRow = sheet.createRow(startRow);
                    tRow.setRowStyle(titleRow.getRowStyle());
                    tRow.setHeight(titleRow.getHeight());
                    //create new title row
                    for (int i = 0; i < 13; i++) {
                        Cell cellCreate = tRow.createCell(i);
                        Cell origiCell = titleRow.getCell(i);
                        if (origiCell != null) {
                            cellCreate.setCellStyle(origiCell.getCellStyle());
                        }
                    }
                    startRow++;
                }

                Cell firstTitleCell = tRow.getCell(0);
                firstTitleCell.setCellValue(specRowIndex);
                Cell secondTitleCell = tRow.getCell(1);
                secondTitleCell.setCellValue(spec.getCaption());
                specRowIndex++;
                DecimalFormat decimalFormat = new DecimalFormat("0.000");
                for (Check check : checks) {
                    if (check.getCheckItemId() <= 0) {
                        continue;
                    }
                    CheckItem checkItem = checkItemRepository.findById(check.getCheckItemId()).get();
                    if (checkItem.getSpecId() != spec.getId()) continue;

                    row = sheet.getRow(startRow);
                    if (row == null) row = sheet.createRow(startRow);
                    row.setRowStyle(srcRow.getRowStyle());
                    row.setHeight(srcRow.getHeight());
                    if (startRow > 3) {
                        for (int i = 0; i < 13; i++) {
                            Cell cellCreate = row.createCell(i);
                            Cell origiCell = srcRow.getCell(i);
                            if (origiCell != null) {
                                cellCreate.setCellStyle(origiCell.getCellStyle());
                            }
                        }
                    }

                    //关键要求
                    cell = row.getCell(1);
                    cell.setCellValue(checkItem.getChekItemDemand());
                    //检查情况
                    cell = row.createCell(11);
                    cell.setCellValue(check.getExpressionDesc());
                    //扣分情况
                    List<CheckCount> checkCounts = checkCountRepository.findByCheckId(check.getId());
                    double mainScoreTotal = 0;
                    boolean mainScoreCheck = true;
                    boolean mainScoreCheck1 = true;
                    List<String> mainPersons = new ArrayList<String>();
                    List<String> mainCorp = new ArrayList<String>();
                    double secondScoreTotal = 0;
                    boolean secondScoreCheck = true;
                    boolean secondScoreCheck1 = true;
                    List<String> secondPersons = new ArrayList<String>();
                    List<String> secondCorp = new ArrayList<String>();
                    double relateScoreTotal = 0;
                    boolean relateScoreCheck = true;
                    boolean relateScoreCheck1 = true;
                    List<String> relatePersons = new ArrayList<>();
                    List<String> relateCorp = new ArrayList<>();
                    for (CheckCount checkCount : checkCounts) {
                        String score = "";
                        if (checkCount.getScoring1() != null) {
                            continue;
                        }
                        if (checkCount.getCorpWeightLevel().trim().equals("主要责任单位")) {
                            //扣分
                            cell = row.createCell(2);
                            //CellStyle style = cell.getCellStyle();
                            //double mainScore = Double.parseDouble(checkCount.getScoring());
                            //mainScoreTotal += mainScore;
                            //cell.setCellValue(mainScoreTotal);
                            if (checkCount.getScoring() != null && mainScoreCheck) {
                                mainScoreTotal += Double.parseDouble(checkCount.getScoring());
                                mainScoreCheck = false;
                            }
                          /*  if(checkCount.getScoring1()!=null&&mainScoreCheck1){
                                mainScoreTotal -=Double.parseDouble(checkCount.getScoring1());
                                mainScoreCheck1=false;
                            }*/
                            cell.setCellValue(decimalFormat.format(mainScoreTotal));
                            //cell.setCellValue(checkCount.getScoring());
                            cell.setCellStyle(srcRow.getCell(2).getCellStyle());
                            //主要责任人
                            if (checkCount.getPostId() > 0) {
                                CorpPost corpPost = corpPostRepository.findById(checkCount.getPostId()).get();
                                cell = row.createCell(4);
                                mainPersons.add(corpPost.getCaption());
                                String mainPersonStr = "";
                                for (String s : mainPersons) {
                                    mainPersonStr = mainPersonStr + s + ",";
                                }
                                mainPersonStr = mainPersonStr.substring(0, mainPersonStr.length() - 1);
                                cell.setCellValue(mainPersonStr);
                            }

                            //主要责任单位
                            CorpProp corpProp = corpPropRepository.getOne(checkCount.getCorpPropId());
                            cell = row.createCell(3);
                            mainCorp.add(corpProp.getCaption());
                            String mainCorpStr = "";
                            for (String s : mainCorp) {
                                mainCorpStr = mainCorpStr + s + ",";
                            }
                            //mainCorpStr = mainCorpStr.substring(0, mainCorpStr.length() - 1);
                            cell.setCellValue(mainCorpStr);
                            cell.setCellValue(corpProp.getCaption());
                        } else if (checkCount.getCorpWeightLevel().trim().equals("次要责任单位")) {
                            //扣分
                            cell = row.createCell(5);
                            //cell.setCellValue(checkCount.getScoring());
                            if (checkCount.getScoring() != null && secondScoreCheck) {
                                secondScoreTotal = secondScoreTotal + Double.parseDouble(checkCount.getScoring());
                                secondScoreCheck = false;
                            }
                          /*  if(checkCount.getScoring1()!=null&&secondScoreCheck1){
                                secondScoreTotal =secondScoreTotal-Double.parseDouble(checkCount.getScoring1());
                                secondScoreCheck1=false;
                            }*/

                            cell.setCellValue(decimalFormat.format(secondScoreTotal));
                            //cell.setCellValue(checkCount.getScoring());
                            //次要责任人
                            if (checkCount.getPostId() > 0) {
                                CorpPost corpPost = corpPostRepository.findById(checkCount.getPostId()).get();
                                cell = row.createCell(7);
                                //cell.setCellValue(corpPost.getCaption());
                                secondPersons.add(corpPost.getCaption());
                                String secondPersonStr = "";
                                for (String s : secondPersons) {
                                    secondPersonStr = secondPersonStr + s + ",";
                                }
                                secondPersonStr = secondPersonStr.substring(0, secondPersonStr.length() - 1);
                                cell.setCellValue(secondPersonStr);
                            }

                            //次要责任单位
                            CorpProp corpProp = corpPropRepository.getOne(checkCount.getCorpPropId());
                            cell = row.createCell(6);
                            secondCorp.add(corpProp.getCaption());
                            String secondCorpStr = "";
                            for (String s : secondCorp) {
                                secondCorpStr = secondCorpStr + s + ",";
                            }
                            secondCorpStr = secondCorpStr.substring(0, secondCorpStr.length() - 1);
                            cell.setCellValue(secondCorpStr);
                            //cell.setCellValue(corpProp.getCaption());
                        } else if (checkCount.getCorpWeightLevel().trim().equals("相关责任单位")) {
                            //扣分
                            cell = row.createCell(8);

                            //cell.setCellValue(checkCount.getScoring());
                            if (checkCount.getScoring() != null && relateScoreCheck) {
                                relateScoreTotal += Double.parseDouble(checkCount.getScoring());
                                relateScoreCheck = false;
                            }
                            // if(checkCount.getScoring1()!=null&&relateScoreCheck1){
                            //     relateScoreTotal -=Double.parseDouble(checkCount.getScoring1());
                            //     relateScoreCheck1=false;
                            // }
                            cell.setCellValue(decimalFormat.format(relateScoreTotal));
                            //相关责任人
                            if (checkCount.getPostId() > 0) {
                                CorpPost corpPost = corpPostRepository.findById(checkCount.getPostId()).get();
                                cell = row.createCell(10);
                                //cell.setCellValue(corpPost.getCaption());
                                relatePersons.add(corpPost.getCaption());
                                String relatePersonStr = "";
                                for (String s : relatePersons) {
                                    relatePersonStr = relatePersonStr + s + ",";
                                }
                                relatePersonStr = relatePersonStr.substring(0, relatePersonStr.length() - 1);
                                cell.setCellValue(relatePersonStr);
                            }
                            //相关主要责任单位
                            CorpProp corpProp = corpPropRepository.getOne(checkCount.getCorpPropId());
                            cell = row.createCell(9);
                            relateCorp.add(corpProp.getCaption());
                            String relateCorpStr = "";
                            for (String s : relateCorp) {
                                relateCorpStr = relateCorpStr + s + ",";
                            }
                            relateCorpStr = relateCorpStr.substring(0, relateCorpStr.length() - 1);
                            cell.setCellValue(relateCorpStr);
                            //cell.setCellValue(corpProp.getCaption());
                        } else {

                        }
                    }
                    startRow++;
                }
            }

            in.close();
            FileOutputStream out = new FileOutputStream(fileName);
            wb.write(out);
            out.close();

            QiniuUtil qiniuUtil = new QiniuUtil();
            qiniuUtil.upload(fileName, keyName);

            return prefix + keyName;
        } catch (IOException e) {
            e.printStackTrace();
            return "server error";
        }
    }

    @ApiOperation(value = "检查表2")
    @RequestMapping(value = "/check2", method = RequestMethod.GET,produces="text/html; charset=UTF-8")
    public String checkReport2(String taskId) {
        String path = "";
        String os = System.getProperty("os.name");
        if (os.toLowerCase().startsWith("win")) {
            path = winPath;
        } else {
            path = linuxPath;
        }
//        String keyName = "检查表 - 副本.xlsx";
        String keyName = "检查表" + GetNowDate() + ".xlsx";
        // String keyName ="jianchabiao"+ GetNowDate() + ".xlsx";
        String fileName = path + keyName;
        downloadSmallFile(check2TempPath, fileName);

        //Task task = taskRepository.getOne(taskId);
        FileInputStream in = null;
        XSSFWorkbook wb = null;
        try {
            in = new FileInputStream(fileName);
            wb = new XSSFWorkbook(in);
            XSSFSheet sheet = wb.getSheet(checkSheetName);
            //XSSFCellStyle cellStyle2 = wb.createCellStyle();
            //XSSFDataFormat format = wb.createDataFormat();
            //cellStyle2.setDataFormat(format.getFormat("@"));
            //检查组织单位
            Row row = null;
            Cell cell = null;
            Row srcRow = sheet.getRow(3);
            Row titleRow = sheet.getRow(2);

//            List<Check> checks = checkRepository. findByTaskIdAndPub(taskId,true);
            int startRow = 3;

            List<Spec> specs = specRepository.findAll();
            short specRowIndex = 1;
            for (Spec spec : specs) {
                Row tRow = null;
                if (specRowIndex == 1) {
                    tRow = titleRow;
                } else {
                    tRow = sheet.createRow(startRow);
                    tRow.setRowStyle(titleRow.getRowStyle());
                    tRow.setHeight(titleRow.getHeight());
                    //create new title row
                    for (int i = 0; i < 10; i++) {
                        Cell cellCreate = tRow.createCell(i);
                        Cell origiCell = titleRow.getCell(i);
                        if (origiCell != null) {
                            cellCreate.setCellStyle(origiCell.getCellStyle());
                        }
                    }
                    startRow++;
                }

                Cell firstTitleCell = tRow.getCell(0);
                firstTitleCell.setCellValue(specRowIndex);
                Cell secondTitleCell = tRow.getCell(1);
                secondTitleCell.setCellValue(spec.getCaption());
                specRowIndex++;
                DecimalFormat decimalFormat = new DecimalFormat("0.00");
                List<Map<String, Object>> checkItemList = reportMapper.getCheckItem(taskId, spec.getId());
                for (Map<String, Object> checkItem : checkItemList) {
                    row = sheet.getRow(startRow);
                    if (row == null) {
                        row = sheet.createRow(startRow);
                    }
                    for (int i = 0; i < 10; i++) {
                        Cell cellCreate = row.createCell(i);
                    }
                    if (row == null) row = sheet.createRow(startRow);
                    Font font = wb.createFont();
                    String checkId = checkItem.get("ID") + "";
                    if (checkId != null && !"null".equals(checkId) && !"".equals(checkId)) {
                        font.setFontName("宋体"); // 字体
                        font.setColor(HSSFColor.RED.index);
                    } else {
                        font.setFontName("宋体"); // 字体
                    }
                    ExcelUtil.createCell(wb, row, (short) 1, checkItem.get("checkItem_Demand"), font);
                    List<CheckItemRule> checkItemRules = checkItemRuleRepository.findByCheckItemId(Long.valueOf(checkItem.get("checkItem_ID") + ""));
                    double weighting=0.0;
                    if (checkItemRules != null && checkItemRules.size() > 0) {
                        ExcelUtil.createCell(wb, row, (short) 2, checkItemRules.get(0).getUnPluesDemo(), font);
                        ExcelUtil.createCell(wb, row, (short) 3, checkItemRules.get(0).getWeighting(), font);
                        CorpPost corpPost = corpPostRepository.getOne( checkItemRules.get(0).getPostId());
                        ExcelUtil.createCell(wb, row, (short) 4, corpPost.getCaption(), font);
                        weighting=Double.parseDouble((checkItemRules.get(0).getWeighting()!=null&&!"".equals(checkItemRules.get(0).getWeighting()))?checkItemRules.get(0).getWeighting():"0");
                    }
                    ExcelUtil.createCell(wb, row, (short) 6, checkItem.get("Expression_Desc"), font);


                    if (checkId != null && !"null".equals(checkId) && !"".equals(checkId)) {
                        List<CheckCount> checkCountList = checkCountRepository.findByCheckId(checkId);
                        if (checkCountList != null && checkCountList.size() > 0) {

                            for (CheckCount checkCount : checkCountList) {
                                if (!org.springframework.util.StringUtils.isEmpty(checkCount.getScoring())) {

                                    ExcelUtil.createCell(wb, row, (short) 5,decimalFormat.format(weighting-Double.parseDouble(checkCount.getScoring())), font);
                                    break;
                                }
                            }

                        }
                        List<FileRecord> fileRecordList = fileRecordRepository.findByCheckIdAndCheckClose(checkId, true);
                        String photoUrl = "";
                        for (FileRecord fileRecord : fileRecordList) {
                            photoUrl += (prefix + fileRecord.getOldPhotoPath() + "\n");
                        }
                        ExcelUtil.createCell(wb, row, (short) 7, photoUrl, font);

                    }
                    startRow++;
                }
            }

            in.close();
            FileOutputStream out = new FileOutputStream(fileName);
            wb.write(out);
            out.close();

            QiniuUtil qiniuUtil = new QiniuUtil();
            qiniuUtil.upload(fileName, keyName);

            return prefix + keyName;
        } catch (IOException e) {
            e.printStackTrace();
            return "server error";
        }
    }

    @ApiOperation(value = "计分表")
    @RequestMapping(value = "/scoresheet2", method = RequestMethod.GET,produces="text/html; charset=UTF-8")
    public String scoresheet2(String taskId) {
        String path = "";
        String os = System.getProperty("os.name");
        if (os.toLowerCase().startsWith("win")) {
            path = winPath;
        } else {
            path = linuxPath;
        }
//        String keyName = "计分表 - 副本.xlsx";
        String keyName = "项目评分表" + GetNowDate() + ".xlsx";
        // String keyName ="xiangmupingfenbiao"+ GetNowDate() + ".xlsx";
        String fileName = path + keyName;
        downloadSmallFile(project2TempPath, fileName);
        DecimalFormat decimalFormat = new DecimalFormat("0.00");
        Task task = taskRepository.getOne(taskId);
        FileInputStream in = null;
        XSSFWorkbook wb = null;
        try {
            in = new FileInputStream(fileName);
            wb = new XSSFWorkbook(in);
            int sheetNo = 0;
            XSSFSheet sheet = wb.getSheetAt(sheetNo);

            //项目名称
            Row row = sheet.getRow(1);
            Cell cell = row.getCell(1);

            Project project = projectRepository.getOne(task.getProjectId());
            cell.setCellValue(project.getCaption());

            //执行时间
            cell = row.createCell(7);
            cell.setCellValue(GetFormatDate(task.getExcuteTime()));

            //建设管理单位
            row = sheet.getRow(4);
            cell = row.getCell(0);
            long corpPropId = 2;
            List<ProjectCorp> projectCorps = projectCorpRepository.findProjectCorpSQL(taskId, task.getProjectId(), corpPropId);
            //List<ProjectCorp> projectCorps = projectCorpRepository.findByCorpPropIdAndProjectId(corpPropId, task.getProjectId());
            Corp corp = corpRepository.getOne(projectCorps.get(0).getCorpId());
            cell.setCellValue(corp.getCaption());

            //监理单位
            corpPropId = 4;
            //List<ProjectCorp> projectCorps1 = projectCorpRepository.findByCorpPropIdAndProjectId(corpPropId, task.getProjectId());
            List<ProjectCorp> projectCorps1 = projectCorpRepository.findProjectCorpSQL(taskId, task.getProjectId(), corpPropId);
            Corp checkCorp = corpRepository.getOne(projectCorps1.get(0).getCorpId());
            cell = row.getCell(3);
            cell.setCellValue(checkCorp.getCaption());

            //施工单位
            corpPropId = 5;
            List<ProjectCorp> projectCorps2 = projectCorpRepository.findProjectCorpSQL(taskId, task.getProjectId(), corpPropId);
            //List<ProjectCorp> projectCorps2 = projectCorpRepository.findByCorpPropIdAndProjectId(corpPropId, task.getProjectId());

            Corp exeCorp = corpRepository.getOne(projectCorps2.get(0).getCorpId());
            cell = row.getCell(6);
            cell.setCellValue(exeCorp.getCaption());

            //建设管理单位岗位
            int buildRowIndex = 7;
            List<CorpPost> buildCorpPosts = corpPostRepository.findByIdBetween(9200, 9300);
            double allBuildTotal = 0;
            Row srcRow = sheet.getRow(buildRowIndex);
            for (CorpPost corpPost : buildCorpPosts) {
                row = sheet.getRow(buildRowIndex);
                if (row == null) row = sheet.createRow(buildRowIndex);
                row.setRowStyle(srcRow.getRowStyle());
                row.setHeight(srcRow.getHeight());
                cell = row.createCell(0);
                cell.setCellValue(corpPost.getCaption());
                List<ProjectPerson> projectPersonList = projectPersonRepository.findProjectPerson(taskId, task.getProjectId(), corpPost.getId());
                Long porjectPersonPostId = 0l;
                if (projectPersonList != null && projectPersonList.size() > 0) {
                    String personName = "";
                    for (ProjectPerson projectPerson : projectPersonList) {
                        Person person = personRepository.getOne(projectPerson.getPersonId());
                        if (person.getCorpId() == corp.getId()) {
                            porjectPersonPostId = projectPerson.getPostId();
                            personName = person.getName();
                            break;
                        }
                    }
                    cell = row.createCell(1);
                    cell.setCellValue(personName);
                }
               /* List<CheckItemRule> checkItemRuleList = checkItemRuleRepository.findByPostId(porjectPersonPostId);
                double totalDenominator = 0;
                for (CheckItemRule checkItemRule : checkItemRuleList) {
                    String score = checkItemRule.getWeighting();
                    if (!StringUtils.isNullOrEmpty(score)) {
                        totalDenominator = totalDenominator + Double.parseDouble(score);
                    }
                }*/
                List<Check> checks = checkRepository.findByTaskIdAndIsPub(taskId, true);

                List<String> chedkIdList = new ArrayList<>();
                for (Check check : checks) {
                    chedkIdList.add(check.getId());
                }
                List<Map<String, Object>> countList = reportMapper.findCheckItemRuleCount(porjectPersonPostId, corp.getId(), chedkIdList);
                double totalDenominator = 0;
                double total = 0;
                for (Map<String, Object> countMap : countList) {
                    Object score = countMap.get("Scoring");
                    Object weighting = countMap.get("Weighting");

                    if (!org.springframework.util.StringUtils.isEmpty(score)) {
                        total += Double.parseDouble(score + "");
                    }
                    if (!org.springframework.util.StringUtils.isEmpty(weighting)) {
                        totalDenominator += Double.parseDouble(weighting + "");
                    }
                }

               /* for (Check check : checks) {
                    List<CheckCount> counts = checkCountRepository.findByCheckIdAndPostId(check.getId(), corpPost.getId());

                    if (counts != null && counts.size() > 0) {
                        for (CheckCount checkCount : counts) {
                            if (checkCount.getCorpId() != corp.getId()) {
                                continue;
                            }
                            String score = checkCount.getScoring();
                            String score2 = checkCount.getScoring1();
                            if (!StringUtils.isNullOrEmpty(score)) {
                                total = total + Double.parseDouble(score);
                            }
                            if (!StringUtils.isNullOrEmpty(score2)) {
                                total = total - Double.parseDouble(score2);
                            }
                        }
                    }

                }*/
                cell = row.createCell(2);
                if (totalDenominator != 0) {
                    if (total == 0) {
                        total = totalDenominator;
                    }
                    cell.setCellValue(decimalFormat.format(total * 100 / totalDenominator));
                    allBuildTotal += total * 100 / totalDenominator;
                }

                buildRowIndex++;
            }
            row = sheet.getRow(4);
            cell = row.getCell(2);
            cell.setCellValue(decimalFormat.format(allBuildTotal / buildCorpPosts.size()));

            //监理单位岗位
            buildRowIndex = 7;
            List<CorpPost> checkCorpPosts = corpPostRepository.findByIdBetween(9400, 9500);
            double allCheckTotal = 0;
            for (CorpPost corpPost : checkCorpPosts) {
                row = sheet.getRow(buildRowIndex);
                if (row == null) row = sheet.createRow(buildRowIndex);
                row.setRowStyle(srcRow.getRowStyle());
                row.setHeight(srcRow.getHeight());
                cell = row.createCell(3);
                cell.setCellValue(corpPost.getCaption());

                List<ProjectPerson> projectPersonList = projectPersonRepository.findProjectPerson(taskId, task.getProjectId(), corpPost.getId());
                //List<ProjectPerson> projectPersonList = projectPersonRepository.findByProjectIdAndPostId(task.getProjectId(), corpPost.getId());
                Long porjectPersonPostId = 0l;
                if (projectPersonList != null && projectPersonList.size() > 0) {
                    String personName = "";
                    for (ProjectPerson projectPerson : projectPersonList) {
                        Person person = personRepository.getOne(projectPerson.getPersonId());
                        if (person.getCorpId() == checkCorp.getId()) {
                            porjectPersonPostId = projectPerson.getPostId();
                            personName = person.getName();
                            break;
                        }
                    }

                    cell = row.createCell(4);
                    cell.setCellValue(personName);
                }
            /*    List<CheckItemRule> checkItemRuleList = checkItemRuleRepository.findByPostId(porjectPersonPostId);
                double totalDenominator = 0;
                for (CheckItemRule checkItemRule : checkItemRuleList) {
                    String score = checkItemRule.getWeighting();
                    if (!StringUtils.isNullOrEmpty(score)) {
                        totalDenominator = totalDenominator + Double.parseDouble(score);
                    }
                }*/
                List<Check> checks = checkRepository.findByTaskIdAndIsPub(taskId, true);
                List<String> chedkIdList = new ArrayList<>();
                for (Check check : checks) {
                    chedkIdList.add(check.getId());
                }
                List<Map<String, Object>> countList = reportMapper.findCheckItemRuleCount(porjectPersonPostId, checkCorp.getId(), chedkIdList);
                double totalDenominator = 0;
                double total = 0;
                for (Map<String, Object> countMap : countList) {
                    Object score = countMap.get("Scoring");
                    Object weighting = countMap.get("Weighting");

                    if (!org.springframework.util.StringUtils.isEmpty(score)) {
                        total += Double.parseDouble(score + "");
                    }
                    if (!org.springframework.util.StringUtils.isEmpty(weighting)) {
                        totalDenominator += Double.parseDouble(weighting + "");
                    }
                }
               /* for (Check check : checks) {
                    List<CheckCount> counts = checkCountRepository.findByCheckIdAndPostId(check.getId(), corpPost.getId());
                    if (counts != null && counts.size() > 0) {
                        for (CheckCount checkCount : counts) {
                            if (checkCount.getCorpId() != checkCorp.getId()) {
                                continue;
                            }
                            String score = checkCount.getScoring();
                            String score2 = checkCount.getScoring1();
                            if (!StringUtils.isNullOrEmpty(score)) {
                                total = total + Double.parseDouble(score);
                            }
                            if (!StringUtils.isNullOrEmpty(score2)) {
                                total = total - Double.parseDouble(score2);
                            }
                        }
                    }
                }*/

                cell = row.createCell(5);
                if (totalDenominator != 0) {
                    if (total == 0) {
                        total = totalDenominator;
                    }
                    cell.setCellValue(decimalFormat.format(total * 100 / totalDenominator));
                    allCheckTotal += total * 100 / totalDenominator;
                }
//                cell.setCellValue(decimalFormat.format(total));
//                allCheckTotal += total;
                buildRowIndex++;
            }
            row = sheet.getRow(4);
            cell = row.getCell(5);
            cell.setCellValue(decimalFormat.format(allCheckTotal / checkCorpPosts.size()));

            //施工单位岗位
            buildRowIndex = 7;
            List<CorpPost> exeCorpPosts = corpPostRepository.findByIdBetween(9500, 9600);
            double allExeTotal = 0;
            for (CorpPost corpPost : exeCorpPosts) {
                row = sheet.getRow(buildRowIndex);
                if (row == null) row = sheet.createRow(buildRowIndex);
                row.setRowStyle(srcRow.getRowStyle());
                row.setHeight(srcRow.getHeight());
                cell = row.createCell(6);
                cell.setCellValue(corpPost.getCaption());
                List<ProjectPerson> projectPersonList = projectPersonRepository.findProjectPerson(taskId, task.getProjectId(), corpPost.getId());
                //List<ProjectPerson> projectPersonList = projectPersonRepository.findByProjectIdAndPostId(task.getProjectId(), corpPost.getId());
                Long porjectPersonPostId = 0l;
                if (projectPersonList != null && projectPersonList.size() > 0) {
                    String personName = "";
                    for (ProjectPerson projectPerson : projectPersonList) {
                        Person person = personRepository.getOne(projectPerson.getPersonId());
                        if (person.getCorpId() == exeCorp.getId()) {
                            porjectPersonPostId = projectPerson.getPostId();
                            personName = person.getName();
                            break;
                        }
                    }
                    cell = row.createCell(7);
                    cell.setCellValue(personName);
                }
            /*    List<CheckItemRule> checkItemRuleList = checkItemRuleRepository.findByPostId(porjectPersonPostId);
                double totalDenominator = 0;
                for (CheckItemRule checkItemRule : checkItemRuleList) {
                    String score = checkItemRule.getWeighting();
                    if (!StringUtils.isNullOrEmpty(score)) {
                        totalDenominator = totalDenominator + Double.parseDouble(score);
                    }
                }*/
                List<Check> checks = checkRepository.findByTaskIdAndIsPub(taskId, true);
                List<String> chedkIdList = new ArrayList<>();
                for (Check check : checks) {
                    chedkIdList.add(check.getId());
                }
                List<Map<String, Object>> countList = reportMapper.findCheckItemRuleCount(porjectPersonPostId, exeCorp.getId(), chedkIdList);
                double totalDenominator = 0;
                double total = 0;
                for (Map<String, Object> countMap : countList) {
                    Object score = countMap.get("Scoring");
                    Object weighting = countMap.get("Weighting");

                    if (!org.springframework.util.StringUtils.isEmpty(score)) {
                        total += Double.parseDouble(score + "");
                    }
                    if (!org.springframework.util.StringUtils.isEmpty(weighting)) {
                        totalDenominator += Double.parseDouble(weighting + "");
                    }
                }
              /*  double total = 0;
                for (Check check : checks) {
                    List<CheckCount> counts = checkCountRepository.findByCheckIdAndPostId(check.getId(), corpPost.getId());
                    if (counts != null && counts.size() > 0) {
                        for (CheckCount checkCount : counts) {
                            if (checkCount.getCorpId() != exeCorp.getId()) {
                                continue;
                            }
                            String score2 = checkCount.getScoring1();
                            String score = checkCount.getScoring();
                            if (!StringUtils.isNullOrEmpty(score)) {
                                total = total + Double.parseDouble(score);
                            }
                            if (!StringUtils.isNullOrEmpty(score2)) {
                                total = total - Double.parseDouble(score2);
                            }
                        }

                    }
                }*/
                cell = row.createCell(8);
                if (totalDenominator != 0) {
                    if (total == 0) {
                        total = totalDenominator;
                    }
                    cell.setCellValue(decimalFormat.format(total * 100 / totalDenominator));
                    allExeTotal += total * 100 / totalDenominator;
                }
//                cell.setCellValue(decimalFormat.format(total));
//                allExeTotal += total;
                buildRowIndex++;
            }
            row = sheet.getRow(4);
            cell = row.getCell(8);
            cell.setCellValue(decimalFormat.format(allExeTotal / exeCorpPosts.size()));

            in.close();
            FileOutputStream out = new FileOutputStream(fileName);
            wb.write(out);
            out.close();

            QiniuUtil qiniuUtil = new QiniuUtil();
            qiniuUtil.upload(fileName, keyName);

            return prefix + keyName;
        } catch (IOException e) {
            e.printStackTrace();
            return e.toString();
        }
    }
    @ApiOperation(value = "计分表")
    @RequestMapping(value = "/scoresheet", method = RequestMethod.GET,produces="text/html; charset=UTF-8")
    public String scoresheet(String taskId) {
        String path = "";
        String os = System.getProperty("os.name");
        if (os.toLowerCase().startsWith("win")) {
            path = winPath;
        } else {
            path = linuxPath;
        }
//        String keyName = "计分表 - 副本.xlsx";
        String keyName = "项目评分表" + GetNowDate() + ".xlsx";
        // String keyName ="xiangmupingfenbiao"+ GetNowDate() + ".xlsx";
        String fileName = path + keyName;
        downloadSmallFile(project2TempPath, fileName);
        DecimalFormat decimalFormat = new DecimalFormat("0.00");
        Task task = taskRepository.getOne(taskId);
        FileInputStream in = null;
        XSSFWorkbook wb = null;
        try {
            in = new FileInputStream(fileName);
            wb = new XSSFWorkbook(in);
            int sheetNo = 0;
            XSSFSheet sheet = wb.getSheetAt(sheetNo);

            //项目名称
            Row row = sheet.getRow(1);
            Cell cell = row.getCell(1);

            Project project = projectRepository.getOne(task.getProjectId());
            cell.setCellValue(project.getCaption());

            //执行时间
            cell = row.createCell(7);
            cell.setCellValue(GetFormatDate(task.getExcuteTime()));

            //建设管理单位
            row = sheet.getRow(4);
            cell = row.getCell(0);
            long corpPropId = 2;
            List<ProjectCorp> projectCorps = projectCorpRepository.findProjectCorpSQL(taskId, task.getProjectId(), corpPropId);
            //List<ProjectCorp> projectCorps = projectCorpRepository.findByCorpPropIdAndProjectId(corpPropId, task.getProjectId());
            Corp corp = corpRepository.getOne(projectCorps.get(0).getCorpId());
            cell.setCellValue(corp.getCaption());

            //监理单位
            corpPropId = 4;
            //List<ProjectCorp> projectCorps1 = projectCorpRepository.findByCorpPropIdAndProjectId(corpPropId, task.getProjectId());
            List<ProjectCorp> projectCorps1 = projectCorpRepository.findProjectCorpSQL(taskId, task.getProjectId(), corpPropId);
            Corp checkCorp = corpRepository.getOne(projectCorps1.get(0).getCorpId());
            cell = row.getCell(3);
            cell.setCellValue(checkCorp.getCaption());

            //施工单位
            corpPropId = 5;
            List<ProjectCorp> projectCorps2 = projectCorpRepository.findProjectCorpSQL(taskId, task.getProjectId(), corpPropId);
            //List<ProjectCorp> projectCorps2 = projectCorpRepository.findByCorpPropIdAndProjectId(corpPropId, task.getProjectId());

            Corp exeCorp = corpRepository.getOne(projectCorps2.get(0).getCorpId());
            cell = row.getCell(6);
            cell.setCellValue(exeCorp.getCaption());

            //建设管理单位岗位
            int buildRowIndex = 7;
            List<CorpPost> buildCorpPosts = corpPostRepository.findByIdBetween(9200, 9300);
            double allBuildTotal = 0;
            Row srcRow = sheet.getRow(buildRowIndex);
            for (CorpPost corpPost : buildCorpPosts) {
                row = sheet.getRow(buildRowIndex);
                if (row == null) row = sheet.createRow(buildRowIndex);
                row.setRowStyle(srcRow.getRowStyle());
                row.setHeight(srcRow.getHeight());
                cell = row.createCell(0);
                cell.setCellValue(corpPost.getCaption());
                List<ProjectPerson> projectPersonList = projectPersonRepository.findProjectPerson(taskId, task.getProjectId(), corpPost.getId());
                Long porjectPersonPostId = 0l;
                if (projectPersonList != null && projectPersonList.size() > 0) {
                    String personName = "";
                    for (ProjectPerson projectPerson : projectPersonList) {
                        Person person = personRepository.getOne(projectPerson.getPersonId());
                        if (person.getCorpId() == corp.getId()) {
                            porjectPersonPostId = projectPerson.getPostId();
                            personName = person.getName();
                            break;
                        }
                    }
                    cell = row.createCell(1);
                    cell.setCellValue(personName);
                }
               /* List<CheckItemRule> checkItemRuleList = checkItemRuleRepository.findByPostId(porjectPersonPostId);
                double totalDenominator = 0;
                for (CheckItemRule checkItemRule : checkItemRuleList) {
                    String score = checkItemRule.getWeighting();
                    if (!StringUtils.isNullOrEmpty(score)) {
                        totalDenominator = totalDenominator + Double.parseDouble(score);
                    }
                }*/
                List<Check> checks = checkRepository.findByTaskIdAndIsPub(taskId, true);

                List<String> chedkIdList = new ArrayList<>();
                for (Check check : checks) {
                    chedkIdList.add(check.getId());
                }
                List<Map<String, Object>> countList = reportMapper.findCheckItemRuleCount2(porjectPersonPostId, corp.getId(),task.getPropId(), chedkIdList);
                double totalDenominator = 0;
                double total = 0;
                for (Map<String, Object> countMap : countList) {
                    Object score = countMap.get("Scoring");
                    Object weighting = countMap.get("Weighting");

                    if (!org.springframework.util.StringUtils.isEmpty(score)) {
                        total += Double.parseDouble(score + "");
                    }
                    if (!org.springframework.util.StringUtils.isEmpty(weighting)) {
                        totalDenominator += Double.parseDouble(weighting + "");
                    }
                }

               /* for (Check check : checks) {
                    List<CheckCount> counts = checkCountRepository.findByCheckIdAndPostId(check.getId(), corpPost.getId());

                    if (counts != null && counts.size() > 0) {
                        for (CheckCount checkCount : counts) {
                            if (checkCount.getCorpId() != corp.getId()) {
                                continue;
                            }
                            String score = checkCount.getScoring();
                            String score2 = checkCount.getScoring1();
                            if (!StringUtils.isNullOrEmpty(score)) {
                                total = total + Double.parseDouble(score);
                            }
                            if (!StringUtils.isNullOrEmpty(score2)) {
                                total = total - Double.parseDouble(score2);
                            }
                        }
                    }

                }*/
                cell = row.createCell(2);
                if (totalDenominator != 0) {
                    if (total == 0) {
                        total = totalDenominator;
                    }
                    cell.setCellValue(decimalFormat.format(total * 100 / totalDenominator));
                    allBuildTotal += total * 100 / totalDenominator;
                }

                buildRowIndex++;
            }
            row = sheet.getRow(4);
            cell = row.getCell(2);
            cell.setCellValue(decimalFormat.format(allBuildTotal / buildCorpPosts.size()));

            //监理单位岗位
            buildRowIndex = 7;
            List<CorpPost> checkCorpPosts = corpPostRepository.findByIdBetween(9400, 9500);
            double allCheckTotal = 0;
            for (CorpPost corpPost : checkCorpPosts) {
                row = sheet.getRow(buildRowIndex);
                if (row == null) row = sheet.createRow(buildRowIndex);
                row.setRowStyle(srcRow.getRowStyle());
                row.setHeight(srcRow.getHeight());
                cell = row.createCell(3);
                cell.setCellValue(corpPost.getCaption());

                List<ProjectPerson> projectPersonList = projectPersonRepository.findProjectPerson(taskId, task.getProjectId(), corpPost.getId());
                //List<ProjectPerson> projectPersonList = projectPersonRepository.findByProjectIdAndPostId(task.getProjectId(), corpPost.getId());
                Long porjectPersonPostId = 0l;
                if (projectPersonList != null && projectPersonList.size() > 0) {
                    String personName = "";
                    for (ProjectPerson projectPerson : projectPersonList) {
                        Person person = personRepository.getOne(projectPerson.getPersonId());
                        if (person.getCorpId() == checkCorp.getId()) {
                            porjectPersonPostId = projectPerson.getPostId();
                            personName = person.getName();
                            break;
                        }
                    }

                    cell = row.createCell(4);
                    cell.setCellValue(personName);
                }
            /*    List<CheckItemRule> checkItemRuleList = checkItemRuleRepository.findByPostId(porjectPersonPostId);
                double totalDenominator = 0;
                for (CheckItemRule checkItemRule : checkItemRuleList) {
                    String score = checkItemRule.getWeighting();
                    if (!StringUtils.isNullOrEmpty(score)) {
                        totalDenominator = totalDenominator + Double.parseDouble(score);
                    }
                }*/
                List<Check> checks = checkRepository.findByTaskIdAndIsPub(taskId, true);
                List<String> chedkIdList = new ArrayList<>();
                for (Check check : checks) {
                    chedkIdList.add(check.getId());
                }
                List<Map<String, Object>> countList = reportMapper.findCheckItemRuleCount2(porjectPersonPostId, checkCorp.getId(),task.getPropId(), chedkIdList);
                double totalDenominator = 0;
                double total = 0;
                for (Map<String, Object> countMap : countList) {
                    Object score = countMap.get("Scoring");
                    Object weighting = countMap.get("Weighting");

                    if (!org.springframework.util.StringUtils.isEmpty(score)) {
                        total += Double.parseDouble(score + "");
                    }
                    if (!org.springframework.util.StringUtils.isEmpty(weighting)) {
                        totalDenominator += Double.parseDouble(weighting + "");
                    }
                }
               /* for (Check check : checks) {
                    List<CheckCount> counts = checkCountRepository.findByCheckIdAndPostId(check.getId(), corpPost.getId());
                    if (counts != null && counts.size() > 0) {
                        for (CheckCount checkCount : counts) {
                            if (checkCount.getCorpId() != checkCorp.getId()) {
                                continue;
                            }
                            String score = checkCount.getScoring();
                            String score2 = checkCount.getScoring1();
                            if (!StringUtils.isNullOrEmpty(score)) {
                                total = total + Double.parseDouble(score);
                            }
                            if (!StringUtils.isNullOrEmpty(score2)) {
                                total = total - Double.parseDouble(score2);
                            }
                        }
                    }
                }*/

                cell = row.createCell(5);
                if (totalDenominator != 0) {
                    if (total == 0) {
                        total = totalDenominator;
                    }
                    cell.setCellValue(decimalFormat.format(total * 100 / totalDenominator));
                    allCheckTotal += total * 100 / totalDenominator;
                }
//                cell.setCellValue(decimalFormat.format(total));
//                allCheckTotal += total;
                buildRowIndex++;
            }
            row = sheet.getRow(4);
            cell = row.getCell(5);
            cell.setCellValue(decimalFormat.format(allCheckTotal / checkCorpPosts.size()));

            //施工单位岗位
            buildRowIndex = 7;
            List<CorpPost> exeCorpPosts = corpPostRepository.findByIdBetween(9500, 9600);
            double allExeTotal = 0;
            for (CorpPost corpPost : exeCorpPosts) {
                row = sheet.getRow(buildRowIndex);
                if (row == null) row = sheet.createRow(buildRowIndex);
                row.setRowStyle(srcRow.getRowStyle());
                row.setHeight(srcRow.getHeight());
                cell = row.createCell(6);
                cell.setCellValue(corpPost.getCaption());
                List<ProjectPerson> projectPersonList = projectPersonRepository.findProjectPerson(taskId, task.getProjectId(), corpPost.getId());
                //List<ProjectPerson> projectPersonList = projectPersonRepository.findByProjectIdAndPostId(task.getProjectId(), corpPost.getId());
                Long porjectPersonPostId = 0l;
                if (projectPersonList != null && projectPersonList.size() > 0) {
                    String personName = "";
                    for (ProjectPerson projectPerson : projectPersonList) {
                        Person person = personRepository.getOne(projectPerson.getPersonId());
                        if (person.getCorpId() == exeCorp.getId()) {
                            porjectPersonPostId = projectPerson.getPostId();
                            personName = person.getName();
                            break;
                        }
                    }
                    cell = row.createCell(7);
                    cell.setCellValue(personName);
                }
            /*    List<CheckItemRule> checkItemRuleList = checkItemRuleRepository.findByPostId(porjectPersonPostId);
                double totalDenominator = 0;
                for (CheckItemRule checkItemRule : checkItemRuleList) {
                    String score = checkItemRule.getWeighting();
                    if (!StringUtils.isNullOrEmpty(score)) {
                        totalDenominator = totalDenominator + Double.parseDouble(score);
                    }
                }*/
                List<Check> checks = checkRepository.findByTaskIdAndIsPub(taskId, true);
                List<String> chedkIdList = new ArrayList<>();
                for (Check check : checks) {
                    chedkIdList.add(check.getId());
                }
                List<Map<String, Object>> countList = reportMapper.findCheckItemRuleCount2(porjectPersonPostId, exeCorp.getId(), task.getPropId(),chedkIdList);
                double totalDenominator = 0;
                double total = 0;
                for (Map<String, Object> countMap : countList) {
                    Object score = countMap.get("Scoring");
                    Object weighting = countMap.get("Weighting");

                    if (!org.springframework.util.StringUtils.isEmpty(score)) {
                        total += Double.parseDouble(score + "");
                    }
                    if (!org.springframework.util.StringUtils.isEmpty(weighting)) {
                        totalDenominator += Double.parseDouble(weighting + "");
                    }
                }
              /*  double total = 0;
                for (Check check : checks) {
                    List<CheckCount> counts = checkCountRepository.findByCheckIdAndPostId(check.getId(), corpPost.getId());
                    if (counts != null && counts.size() > 0) {
                        for (CheckCount checkCount : counts) {
                            if (checkCount.getCorpId() != exeCorp.getId()) {
                                continue;
                            }
                            String score2 = checkCount.getScoring1();
                            String score = checkCount.getScoring();
                            if (!StringUtils.isNullOrEmpty(score)) {
                                total = total + Double.parseDouble(score);
                            }
                            if (!StringUtils.isNullOrEmpty(score2)) {
                                total = total - Double.parseDouble(score2);
                            }
                        }

                    }
                }*/
                cell = row.createCell(8);
                if (totalDenominator != 0) {
                    if (total == 0) {
                        total = totalDenominator;
                    }
                    cell.setCellValue(decimalFormat.format(total * 100 / totalDenominator));
                    allExeTotal += total * 100 / totalDenominator;
                }
//                cell.setCellValue(decimalFormat.format(total));
//                allExeTotal += total;
                buildRowIndex++;
            }
            row = sheet.getRow(4);
            cell = row.getCell(8);
            cell.setCellValue(decimalFormat.format(allExeTotal / exeCorpPosts.size()));

            in.close();
            FileOutputStream out = new FileOutputStream(fileName);
            wb.write(out);
            out.close();

            QiniuUtil qiniuUtil = new QiniuUtil();
            qiniuUtil.upload(fileName, keyName);

            return prefix + keyName;
        } catch (IOException e) {
            e.printStackTrace();
            return e.toString();
        }
    }

    public String GetNowDate() {
        String temp_str = "";
        Date dt = new Date();
        //最后的aa表示“上午”或“下午”    HH表示24小时制    如果换成hh表示12小时制
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
        temp_str = sdf.format(dt);
        count++;
        temp_str = temp_str + String.valueOf(count);
        return temp_str;
    }

    public String GetFormatDate(Date dt) {
        if (dt == null) {
            return "";
        }
        String temp_str = "";
        //最后的aa表示“上午”或“下午”    HH表示24小时制    如果换成hh表示12小时制
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy年MM月dd日");
        temp_str = sdf.format(dt);
        return temp_str;
    }

    public boolean downloadSmallFile(String uri, String filePath) {
        OkHttpClient client = new OkHttpClient();
        Request request = new Request.Builder().url(uri.toString()).build();

        try {
            Response response = client.newCall(request).execute();
            if (!response.isSuccessful()) {
                return false;
            }

            ResponseBody body = response.body();
            long contentLength = body.contentLength();
            BufferedSource source = body.source();
            File file = new File(filePath);
            BufferedSink sink = Okio.buffer(Okio.sink(file));
            sink.writeAll(source);
            sink.flush();
        } catch (IOException e) {
            e.printStackTrace();
            return false;
        }

        return true;
    }

}
